﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Helpers;

namespace ChinaDynastyTours.Controls
{
    public class GuideNotifiedCtrl
    {

        #region 导游通知

        public static DataTable GetGuideNotifiedData(string tourGroupId, string language, string name, string ciceroniId,string notified, string startDate, string endDate)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string szCondition = "";

            #region 构建条件语句
		    
            if (tourGroupId != "")
                szCondition = "TourGroupID='" + tourGroupId + "'";

            if (szCondition != "")
            {
                if (language != "")
                    szCondition += " AND CiceroniLanguage='" + language + "'";
            }
            else
            {
                if (language != "")
                    szCondition += " CiceroniLanguage='" + language + "'";
            }
            if (szCondition != "")
            {
                if (name != "")
                    szCondition += " AND OwnerA='" + name + "'";
            }
            else
            {
                if (name != "")
                    szCondition += " OwnerA='" + name + "'";
            }

            if (szCondition != "")
            {
                if (ciceroniId != "")
                    szCondition += " AND CiceroniId=" + ciceroniId ;
            }
            else
            {
                if (ciceroniId != "")
                    szCondition += " CiceroniId=" + ciceroniId;
            }

            if (szCondition != "")
            {
                if (notified != "")
                    szCondition += " AND Notified=" + notified;
            }
            else
            {
                if (notified != "")
                    szCondition += " Notified=" + notified;
            }
            //if (szCondition != "")
            //{
            //    if (departmentId != "")
            //        szCondition += " AND ((OwnerA in (SELECT name FROM aspnet_UserInfo WHERE DepartmentID='" + departmentId + "')) OR (OwnerB in (SELECT name FROM aspnet_UserInfo WHERE DepartmentID='"+ departmentId +"')))";
            //}
            //else
            //{
            //    if (departmentId != "")
            //        szCondition += " ((OwnerA in (SELECT name FROM aspnet_UserInfo WHERE DepartmentID='" + departmentId + "')) OR (OwnerB in (SELECT name FROM aspnet_UserInfo WHERE DepartmentID='" + departmentId + "')))";

            //}


            #endregion

            string sql = string.Format("SELECT * FROM vw_GuideNotified  WHERE convert(varchar(10),StartDate,121) BETWEEN '{0}' AND '{1}' AND OwnerProviderID='{2}'", startDate, endDate,ApplicationHelper.CurrentLoginProviderNameAndID[1]);

            if (szCondition != "")
                sql += " AND " + szCondition + " ORDER BY StartDate";
            else
                sql += " ORDER BY StartDate";
            
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion

    }
}
